<?php
namespace Administrador\Model\Entity;
use Zend\Db\TableGateway\TableGateway;
use Zend\Db\Adapter\Adapter;

class Base extends TableGateway
{
  private $odbc;
  
  public function __construct($odbc)
  {
    $this->odbc = $odbc;
  }
  
  //permite leer catalogos dinamicamente
  public function leerCatalogos($tbl)
  {
    $arr = array("state_nombre","country_nombre");
    $catalogo = new TableGateway($tbl,$this->odbc);
    $data = $catalogo->select();
    $data = $data->toArray();
    $data = json_encode($data);
    $data = str_replace($arr,"value",$data);
    return  array("data"=>$data);  
  }
  
  //permite insertat nuevas plabras al diccionario 
  public function insertDiccionario($word,$abreviatura,$pais)
  {
    $insert = new TableGateway("cat_diccionario",$this->odbc);
    $data = array("word"=>$word,"abbreviation"=>$abreviatura,"abbreviation2"=>$pais);
    $insert->insert($data);
    return array("data"=>json_encode($data));
  }
  
  //permite almacenar los datos recolectados del CVS
  public function insertaCVS($json)
  {
    $insert = new TableGateway("email_user",$this->odbc);
    $aux = json_decode($json);
    $result = 0;
    $result2=0;
    foreach($aux as $array):
      
      $arrSalida = (array)$array;
      //valido que no exista el conetenido
      $select = $insert->getSql()->select();
      
      $select->where(array("externalId"=>$arrSalida["externalId"]));
      $exec = $insert->selectWith($select);
      $row = $exec->current();
      if(!$row):
        $result += $insert->insert($arrSalida);
      else:
         $insert->update($arrSalida,array("externalId"=>$arrSalida["externalId"]));
         $result2 ++;         
      endif;
    endforeach;
    return array("data"=>$result."-".$result2);
  }
  
  //consigiendo las estadisticas para las geocharts
  public function charGeo()
  {
    $select = new TableGateway("total_states",$this->odbc); 
    $data =  $select->select();
    
    $dato = $data->toArray();
    return $dato;        
  }
   //numero de usuarios
   public function contUser()
   {
     $select = new TableGateway("email_user",$this->odbc);
     $data =  $select->select();
     
     
     //totales para la estadistica
     $tUsuarios = $data->count();
     $tValidMail = 0;
     $tOption = 0;
     $tNewsletter = 0;
     $contp = 0;
     
     
     $data = $data->toArray();
     
     foreach($data as $datos):
      
      //mail validos
      if($datos["validEmail"] == 0):
        $tValidMail ++; 
      endif;
      
      //opciones registradas
      if($datos["optinA"] > 0):
        $tOption ++;
      endif;
      
      /*if($datos["optinN"] > 0):
        $tOption ++;
      endif;
      
      if($datos["optinS"] > 0):
        $tOption ++;
      endif;*/

      //registro en news letter      
      if($datos["newsletter"] != "NULL"):
        $tNewsletter ++;
      endif;
     endforeach;
     
     //consegimos datos para el manejo de las estadisticas por pais
      $conteo = $this->charGeo(); 
      $arr2 = "";
      $arr = array();
      foreach($conteo as $datos):
        $arr[$contp] = $datos["country"].",".$datos["country_nombre"];        
        $arr2 .=  $datos["state_nombre"]."-".$datos["total_state"]."-".$datos["totalOptian"]."-".$datos["country"].',';
        $contp++;
      endforeach;  
      
     if(is_array($arr)):
      $aux = array_unique($arr);
     else:
      $aux = 0;
     endif;
     
     $data = array(
      "Tusuarios"=>number_format($tUsuarios),
      "Toption" => number_format($tOption),
      "Tvalidmail" => number_format($tValidMail),
      "Tnews" => number_format($tNewsletter),
      "Tstats"=> ($arr2),
      "Tpais" => $aux
     );
     return $data;
   } 

  //funcion para refrescar el contenido del por rango de fechas para el dash board
  public function rangoFecha($datInicio,$datFin)
  {
    date_default_timezone_set('UTC');
    $sp =  new TableGateway('states', $this->odbc);
    $sp2 = $sp->adapter;
    $data1 = "2011-12-21 00:00:00";
    $data2 = "2011-12-21 23:59:59";
    $stmt = $sp2->createStatement("CALL estadisticas('".$datInicio."','".$datFin."')"); 
    $result = $stmt->execute();
    $output = $result->current();
    $validMail = 0;
    $totalNew = 0;

    if($output["salida"] == "0"):
    
      $json = array("msg"=>0);
    
    else:
      $aux = explode("|",$output["salida"]);
      $json =  array(
                      "tbl"=>$aux[0],
                      "cont"=>$aux[1],
                      "country"=>$aux[2],
                      "msg"=>1
                    );    
    endif;
    $salida = array("data"=>json_encode($json));  

    return $salida;
  }

  //consigiendo las estadisticas para las geocharts
  public function charPie()
  {
    $select = new TableGateway("total_optian",$this->odbc); 
    $data =  $select->select();
    
    $dato = $data->toArray();
    return $dato;        
  }



  //consigo las estadisticas para las opciontes de optinA 
  public function optin()
  {
    $select  = new TableGateway('email_user', $this->odbc);
    $char = $this->charPie();
    
    $aux = $select->select(array("optinA"=>1,"state"=>"NU"));
    $Tnull = $aux->count();
    
    
    $Toptian = 0;
    $Tmail = 0;
    $cont = 0;
    $str = "";
    $arr = array();
    //consigo el total de options valid mail 
    foreach($char as $datos):
      $Toptian += $datos["totalOptian"];
      $Tmail += $datos["validMail"];
      $arr[$cont] = $datos["country"].",".$datos["country_nombre"]; 
      $str .= $datos["state_nombre"]."-".$datos["total_state"]."-".$datos["totalOptian"]."-".$datos["country"].',';
      $cont++;
    endforeach;
    
    $str .= "No State-".$Tnull."-".$Toptian."-NULL"; 
    
    if(is_array($arr)):
      $aux = array_unique($arr);
    else:
      $aux = 0;
    endif;
   
    $salida = array(
                      "Toptin" => number_format($Toptian),
                      "Tmail"   => number_format($Tmail),
                      "Tnull"   => number_format($Tnull),
                      "Tpais"   => $aux,
                      "Tstats"  => $str,  
                      "Tdieta"  => 0,
                      "Thola"   => 0
                    );
    return $salida;
  }
 
  //funcion que permite filtrar por fechas para la pantalla de optin
  public function rangoFechaOptin($datInicio,$datFin)
  {
    date_default_timezone_set('UTC');
    $sp =  new TableGateway('', $this->odbc);
    $sp2 = $sp->adapter;
    //$datInicio = '2011-12-21 00:00:00';
    //$datFin = '2013-01-10 23:59:59';
    $stmt = $sp2->createStatement("CALL estadisticas_optin('".$datInicio."','".$datFin."')"); 
    $result = $stmt->execute();
    $output = $result->current();

    if($output["salida"] == "0"):
    
      $json = array("msg"=>0);
    
    else:
      $aux = explode("|",$output["salida"]);
      $json =  array(
                      "tbl"=>$aux[0],
                      "cont"=>$aux[1],
                      "country"=>$aux[2],
                      "msg"=>1
                    );    
    endif;
    $salida = array("data"=>json_encode($json));  

    return $salida; 
  }
  /*funcion para conseguir los registros que no tiene estados*/
  private function newsNull()
  {
    $sp  = new TableGateway('email_user', $this->odbc);
    $sp2 = $sp->adapter;
    $stmt = $sp2->createStatement('SELECT count(state) AS total FROM bd_estadisticas.email_user WHERE newsletter != "NULL" AND state = "NU" AND country = "NU";');
    $result = $stmt->execute();
    $output = $result->current();
    return $output["total"];
  }
 
 
 
  /* funcion para cargar las estadisticas iniciales */
  public function newsletter()
  {
    $select  = new TableGateway('vnewsletter', $this->odbc);
    $data = $select->select();
    $data = $data->toArray();
    
    $TSub = 0;
    $Tboun = 0;    
    $TUnsub = 0;
    $TNull = $this->newsNull();
    
    $str = "";
    $arr = array();
    $cont = 0;
    
    foreach($data as $datos):
      $arr[$cont] = $datos["country"]."-".$datos["country_nombre"];
      $TSub += $datos["total_state"];
      $Tboun += $datos["bounced"];
      $TUnsub += $datos["unsuscrib"];
      $str .= $datos["state_nombre"]."-".$datos["total_state"]."-".$datos["bounced"]."-".$datos["country"].',';
      $cont++;
    endforeach;
   $aux_ = (($Tboun-$TNull)); 
   if($aux_ < 0)
   {$aux_ *= -1;}
    
    
    
    $str .= "No State-".$TNull."-".$aux_."-NULL"; 

    if(is_array($arr)):
      $aux = array_unique($arr);
    else:
      $aux = 0;
    endif;
   
   $aux_ = (($Tboun-$TNull)); 
   if($aux_ < 0)
   {$aux_ *= -1;}
    
   $salida = array("TSub"=>($TSub+$TNull),
                    "TBoun"=>($aux_+$Tboun),
                    "TUnsub"=>($TUnsub),
                    "TState"=>($TSub),
                    "Tpais"=>$aux,
                    "TNew"=>$str
                  );
    return $salida;  
  }
 
  /** funcion para el manejo del rango de fechas newsletter **/
  public function rangoFechaNews($datInicio,$datFin)
  {
    date_default_timezone_set('UTC');
    $sp =  new TableGateway('', $this->odbc);
    $sp2 = $sp->adapter;
    //$datInicio = '2011-12-21 00:00:00';
    //$datFin = '2011-12-21 23:59:59';
    
    $stmt = $sp2->createStatement("CALL estadisticas_newsletter('".$datInicio."','".$datFin."')"); 
    $result = $stmt->execute();
    $output = $result->current(); 
    
    if($output["salida"] == "0"):
      $json = array("msg"=>0);
    else:
      $aux = explode("|",$output["salida"]);
      $json = array(
                "tbl"=>$aux[0],
                "cont"=>$aux[1],
                "country"=>$aux[2],
                "msg"=>1
              );
      
    endif;
    $salida = array("data"=>json_encode($json));  
    return $salida;    
    
  }  
 
   /***********************************************************************************/
  //me permite realizar la lectura de los catalogos asi como atender peticiones ajax
  public function catalogos($post)
  {
    $opt = $post["tabla"];
    switch($opt)
    {
      case "1":
        $datos = $this->leerCatalogos("states");
      break;
      
      case "2":
        $datos = $this->leerCatalogos("country");
      break;
      
      case "3":
        $word = $post["word"];
        $ebreviatura = $post["abreviatura"];
        $pais = $post["pais"];
        $datos = $this->insertDiccionario($word,$ebreviatura,$pais);
      break;
      
      case "4":
        $json =  $post["arreglo"];
        $datos = $this->insertaCVS($json);
      break;
      
      case "5":
        date_default_timezone_set('UTC');
        $datInicio = $post["datInicio"]." 00:00:00";
        $datFin = $post["datFinal"]." 23:59:59";
        $datos = $this->rangoFecha($datInicio,$datFin);
      break;
      
      case "6":
        date_default_timezone_set('UTC');
        $datInicio = $post["datInicio"]." 00:00:00";
        $datFin = $post["datFinal"]." 23:59:59";
        $datos = $this->rangoFechaOptin($datInicio,$datFin);  
      break;
      
      case "7":
        date_default_timezone_set('UTC');
        $datInicio = $post["datInicio"]." 00:00:00";
        $datFin = $post["datFinal"]." 23:59:59";
        $datos = $this->rangoFechaNews($datInicio,$datFin);         
      break;
    }
    return $datos;
  }
}

?>